Oracle OLAP
Generating Views for Oracle OLAP
Release 10.2.0.3.0

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Copyright © 2007, Oracle. All rights reserved.

OLAP View Generator Sample 1.0.2

• Introduction
• Sample Description
• Installing the Sample

Introduction

The purpose of this sample is to provide an easy way to define relational views over dimensions and cubes in an analytic workspace. The sample delivers two ways to perform this task:

1. A plug-in to Analytic Workspace Manager (AWM) 10.2.0.3.
2. A PL/SQL package - enabling the functions to be called from a wide variety of clients.

The AWM plug-in requires the PL/SQL packages to define views.

Sample Description

Defining a relational view over an analytic workspace requires the following steps:

• Define a limitmap:
  A limitmap is a key parameter to the OLAP_TABLE function. The OLAP_TABLE function manages the selection and retrieval of data from an analytic workspace via a relational view. The limitmap specifies how a relational view's columns map to the multidimensional objects in the cube. For further details on these features, refer to the Oracle OLAP Reference.
• Store the limitmap definition in a variable in the analytic workspace.
• Define a relational view using the OLAP_TABLE function and the limitmap.

This sample automates the process described above.

Creating Views with AWM 10.2.0.3

The capabilities of AWM 10.2.0.3 may be extended using plug-ins (more details on plug-ins may be found on OTN). This sample provides an AWM plug-in that allows for the creation of views over dimensions and cubes. To create a view, simply right-click on a dimension or cube in the AWM navigator pane and select Plug-in - Create Relational View... You will be presented with the following dialog:

Select or deselect the columns that you would like to include in the view, optionally change the name of the view's column and then click Create View. A view over the selected item will be defined in the same schema as the current analytic workspace. Note: the tool uses the PL/SQL API described below to define the view.

Creating Views Using PL/SQL Functions

This sample provides numerous functions and procedures to facilitate this view definition process. All of the database objects required for this sample are owned by the OLAP_VIEWS user. The code can be found in the OLAP_ViewGenerator package.

There are three procedures that create views over analytic workspace objects. You can use products like SQL Developer or SQL Plus to run these procedures. To run the procedures, log into the database as a user that has update privileges on the source analytic workspace (e.g. the owner of the AW). If you are using SQL Plus, remember to issue the command set serveroutput on in order to see progress and error messages:

• createDimensionView (strOwner varchar2, strAWname varchar2, strDimname varchar2)
  This procedure creates a view over a dimension using default column names. It takes three parameters as arguments: 1) the AW owner (i.e. schema where the AW is stored), 2) the analytic workspace name and 3) the dimension name.
  
  For example, to create a view over the product dimension in the global_aw analytic workspace owned by user global:
  execute olap_views.olap_viewGenerator.createDimensionView ('GLOBAL', 'GLOBAL_AW', 'PRODUCT');
  
  
• createCubeView (strOwner varchar2, strAWname varchar2, strCubename varchar2)
  This procedure creates a view over a cube using default column names. It takes three parameters as arguments: 1) the owner, 2) the analytic workspace name and 3) the cube name.
  
  For example, to create a view over the cube units_cube in the global_aw analytic workspace owned by user global:
  execute olap_views.olap_viewGenerator.createCubeView ('GLOBAL', 'GLOBAL_AW', 'UNITS_CUBE');
  
  
• createAWViews (strOwner varchar2, strAWname varchar2)
  This procedure creates views for all of the dimensions and cubes in the analytic workspace. It takes two parameters as arguments: 1) the owner and 2) the analytic workspace name
  
  For example, to create a view over the cube units_cube in the global_aw analytic workspace owned by user global:
  execute olap_views.olap_viewGenerator.createAWViews ('GLOBAL', 'GLOBAL_AW');

Reviewing the OLAP View Definitions

The information about the views are contained in three tables. Public synonyms have been created for these tables using the simple table name:

• OLAP_VIEW_OBJECTS
  Contains a list of the views that have been created by the view generator. In addition, it lists the objects (dimensions and cubes) that are referenced by the views.
• OLAP_VIEW_LIMITMAP
  Contains the limitmap that was used as part of the view definition.
• OLAP_MAPPINGS
  Contains all the information required to generate a limitmap for a cube or dimension. This includes column names, analytic workspace objects that map to the column names, the limitmap clause that the analytic workspace object is referenced by, etc.

You can generate a report of all of the views that have been created over the AW - including how columns are mapped to AW objects - by calling the following procedure: reportViewInfo (strOwner varchar2, strAWname varchar2). For example:

execute olap_views.olap_viewGenerator.reportViewInfo ('GLOBAL', 'GLOBAL_AW');

Other Useful Procedures and Functions

Additional procedures and functions are available to enable customization of the view definitions - either manually or by an application. These programs are called by the create view functions:

• generateDimensionMap and generateCubeMap
  These functions populate the OLAP_MAPPINGS table with default mappings of relational view columns to AW objects for dimensions and cubes.
• getDimensionLimitmap and getCubeLimitmap
  These functions return the limitmap for a dimension or cube. The limitmap is generated based on the information contained in the OLAP_MAPPINGS table. Changes that were made to the default OLAP_MAPPING settings prior to calling these functions will be reflected in the generated limitmap. getDimensionLimitmap and getCubeLimitmap will refresh the OLAP_MAPPINGS table (i.e. they call the generateDimensionMap and generateCubeMap functions) if changes were made in a previous database session.
  

For example, if you would like to 1) change the default column names for product dimension and then 2) create a view over the dimension:

execute olap_views.olap_viewGenerator.generateDimensionMap('GLOBAL', 'GLOBAL_AW', 'PRODUCT');
update olap_mappings set column_name = xxxx ...
execute olap_views.olap_viewGenerator.createDimensionView('GLOBAL', 'GLOBAL_AW', 'PRODUCT');

Installing the Sample

There are two parts to the sample installation:

1. Install the PL/SQL package
2. Install the AWM plug-in

Install the PL/SQL package

The sample installation will create the OLAP_VIEWS user and install the database objects into the schema. To install the sample, connect to the database as a user who has rights to create users and assign privileges - and run the PL/SQL script install_olap_view_generator.sql. For example:

sqlplus system/manager
SQL> @install_olap_view_generator.sql

The sample installation assumes that the database is local. If it is not local, modify the following line in the install_olap_view_generator.sql script to include the appropriate connect string:

connect olap_views/olap_views

Install the AWM Plug-in

Copy the viewGenerator.jar file to AWM Plug-in directory. Enable plug-ins and specify the plug-in directory location by selecting the AWM menu item Tools - Configuration...

Copyright © 2007 Oracle Corporation
All Rights Reserved